﻿CREATE VIEW [dbo].[BAM_837I_835]
AS 
	
SELECT 
	 BTSMessageID
	,BTSInterchangeID
	,MessageID, 'TransactionStatus' = 
     CASE
          WHEN 
				(select 
					count(*) 
				from 
					dbo.BAM_835 b835 
				where 
					b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) = 1 THEN 'Completed'
		  WHEN 
				(select 
					count(*) 
				from 
					dbo.BAM_835 b835 
				where 
					b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) > 1 THEN 'Duplicate BHT03 ID'
          ELSE 'Incomplete'
     END
	,(Select top 1 
			BTSMessageID 
	  from 
			dbo.BAM_835 b835 
	  where 
			b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) 
	  as 'BTSMessageID_835'
	,(Select top 1 
			BTSInterchangeID 
	  from 
			dbo.BAM_835 b835 
	  where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) as 'BTSInterchangeID_835'

from BAM_837I b837I
;
--Drop VIEW [dbo].[BAM_837I_835]